"""
数据库操作案例
  数据库操作库 sqlalchemy  ORM框架
  pip list
  pip install SQLAlchemy
  pip install pymysql
"""
from fastapi import FastAPI
from sqlalchemy import create_engine, Column, Integer, String, or_, func, text
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
import uvicorn
from fastapi.staticfiles import StaticFiles
import os

#连接地址 数据库+数据库驱动类：//用户名：密码@数据库地址：端口号/数据库名？连接参数
engin = create_engine(
    "mysql+pymysql://web2024:123123@127.0.0.1:3306/web2024?charset=utf8mb4",
    echo=True
)
session = sessionmaker(
    bind=engin,
    autoflush=False,
    autocommit=False
)

db = session()


Base = declarative_base()

class User(Base):
    __tablename__ = 'user'

    id = Column(Integer, primary_key=True, autoincrement=True, comment="ID")
    username = Column(String(255),nullable=True, comment="用户登录名")
    realname = Column(String(255),nullable=True, comment="用户姓名")
    password= Column(String(255),nullable=True, comment="登陆密码")
    gender=Column(Integer, default=1, comment="性别 1男2女")
    desp=Column(String(255),nullable=True, comment="简介")


app = FastAPI()

# 通过对象申城表格
# Base.metadata.create_all(bind=engin)

path = os.path.join(os.getcwd(), "example10/www")

app.mount("/www", StaticFiles(directory=path),name="www")

@app.get("/insert", tags=['数据库增删改查'])
def insert_test():
    # 增加数据
    # insert into (username, realname, password, gender) values ('xxx','yyy','123',1)
    newUser = User(username="test", realname="张三",password="123123", gender=1)
    db.add(newUser)
    db.commit()

    #db.execute(text("insert into ..."))
    return {"code":200, "msg":"OK"}

@app.get("/delete", tags=['数据库增删改查'])
def delete_test():
    db.query(User).filter(User.id==1).delete()
    db.commit()
    db.rollback()
    return {"code":200, "msg":"OK"}

@app.get("/update", tags=['数据库增删改查'])
def update_test():
    db.query(User).filter(User.id==2).update({"realname":"李四", "gender":2})
    db.commit()
    return {"code":200, "msg":"OK"}

@app.get("/select", tags=['数据库增删改查'])
def select_test():
    #查询多条
    res = db.query(User).filter(User.username=='test').all()
    #分页查询
    res = db.query(User).offset(0).limit(10).all()
    # return res
    #差选单条
    res = db.query(User).filter(User.username=='test').first()
    #排序 降序在字段名后加.desc()
    res = db.query(User).filter(User.username=='test').order_by(User.id.desc()).all()
    
    #分组
    res = db.query(User).group_by(User.username).all()

    #and和or条件
    # select * from user where realname='张三' and (username='test' or gender=1)
    res = db.query(User).filter(
        or_(
            User.username=='test',
            User.gender == 1
        ),
        User.realname=='张三'

    ).all()
    # like in
    res = db.query(User).filter(User.realname.like('%张%')).all()
    res = db.query(User).filter(User.gender.in_([1,2]))
    # 计数 count
    res = db.query(func.count(User.id)).first()

    return {"code":200, "msg":"OK"}

if __name__ == '__main__':
    uvicorn.run(
        app     = 'example10:app', 
        host    = "0.0.0.0", 
        port    = 8088, 
        reload  = True
    )

